package com.gticket.customerbackend.mapper;

import com.gticket.customerbackend.pojo.Statistics;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StatisticsMapper {

    @Select("SELECT movies.movie_name,count(*) quantity,sum(movies.movie_price) totalPrice\n" +
            "FROM ${database}.filmschedule filmschedule,${database}.movies movies,${database}.orderitems orderitems,${database}.orders orders\n" +
            "WHERE movies.movie_id = filmschedule.movie_id \n" +
            "and filmschedule.fs_id = orderitems.fs_id \n" +
            "and orderitems.order_id = orders.order_id \n" +
            "and filmschedule.flag=0\n" +
            "and orderitems.flag=0\n" +
            "and orders.flag=0\n" +
            "and movies.flag=0\n" +
            "and DATE_FORMAT(create_time,'%Y-%m-%d')=date_format(#{oneDay},'%Y-%m-%d')\n" +
            "and orders.order_state=1\n" +
            "GROUP BY movies.movie_name")
    List<Statistics> listStatistics(Statistics Statistics);

    @Select("SELECT movies.movie_name,count(*) quantity,sum(movies.movie_price) totalprice,DATE_FORMAT(orders.create_time,'%Y-%m-%d') time\n" +
            "FROM ${database}.filmschedule filmschedule,${database}.movies movies,${database}.orderitems orderitems,${database}.orders orders\n" +
            "WHERE movies.movie_id = filmschedule.movie_id \n" +
            "and filmschedule.fs_id = orderitems.fs_id \n" +
            "and orderitems.order_id = orders.order_id \n" +
            "and filmschedule.flag=0\n" +
            "and orderitems.flag=0\n" +
            "and orders.flag=0\n" +
            "and movies.flag=0\n" +
            "and orders.order_state=1\n" +
            "and movies.movie_name= #{movie_name} \n" +
            "and create_time >= #{start_time} and create_time <= #{end_time} \n" +
            "GROUP BY time\n")
    List<Statistics> listTimes(Statistics statistics);

    @Select("select * from ${database}.movies where flag=0")
    List<Statistics>  listMovies(Statistics statistics);
}
